IF OBJECT_ID('dbo.fnGetFactTableBudget') IS NOT NULL
BEGIN
    DROP FUNCTION dbo.fnGetFactTableBudget
    IF OBJECT_ID('dbo.fnGetFactTableBudget') IS NOT NULL
        PRINT '<<< FAILED DROPPING FUNCTION dbo.fnGetFactTableBudget >>>'
    ELSE
        PRINT '<<< DROPPED FUNCTION dbo.fnGetFactTableBudget >>>'
END
go

-- =======================================================================
-- Function Name: dbo.fnGetFactTableBudget()
--
-- Author:      Kevin Cashman, Parallax Consulting LLC
--
-- Create date: 10/10/2008
--
-- Description: Returns Table of BudgetPeriods and Amounts
--             for a given Search Date range for Reports.
--             Derived from dbo.fnGetPeriodBudget
--
-- Parameters:  @StartDate, @EndDate 
--              Search Params for Reports
--              
-- Returns:     TABLE (see defn below) 
--
-- SampleCall:
-- SELECT * FROM dbo.fnGetFactTableBudget ( @StartDate, @EndDate )
--
--------------------------------------------------------------------------
-- Date        Initials  Modification
--------------------------------------------------------------------------
-- 10/10/08    KC        Created
-- 11/12/08    KC        Changed CashBalance to get from FCT.CashBalance
-- 11/13/08    KC        Added Driect and Indirect Expense Columns
--
-- =======================================================================
CREATE FUNCTION dbo.fnGetFactTableBudget
(
     @StartDate     DATETIME, 
     @EndDate       DATETIME
)
RETURNS @PeriodBudget TABLE
(
     AgreementId              INTEGER,
     BudgetYear               INTEGER,
     FundNumber               VARCHAR(20),
     ProjectStartDate         DATETIME,
     ProjectEndDate           DATETIME,
     ProjectAwardedAmount     MONEY,
     BudgetStartDate          DATETIME,
     BudgetEndDate            DATETIME,
     ExpenseAmount            MONEY,
     IndirectExpenses         MONEY,
     DirectExpenses           MONEY,
     CashBalance              MONEY
)
AS
BEGIN
     INSERT    @PeriodBudget
               (AgreementId,
               BudgetYear,
               FundNumber,
               ProjectStartDate,
               ProjectEndDate,
               ProjectAwardedAmount,
               BudgetStartDate,
               BudgetEndDate,
               ExpenseAmount,
               IndirectExpenses,
               DirectExpenses,
               CashBalance)
     SELECT    SRCHBGT.AgreementId,
               SRCHBGT.BudgetYear,
               VBGT.FundNumber,
               PROJ.ProjectStartDate,
               PROJ.ProjectEndDate,
               PROJ.AwardedAmount,
               VBGT.BudgetStartDate,
               VBGT.BudgetEndDate,
               VBGT.DirectExpenses + VBGT.IndirectExpenses,
               VBGT.DirectExpenses,
               VBGT.IndirectExpenses,
               VBGT.CashBalance
     FROM               
     (SELECT   VWB.AgreementId,
               MAX(VWB.MAXYear) AS BudgetYear
     FROM          
     (SELECT    AgreementId,
               MAX(BudgetYear) AS MAXYear
     FROM      dbo.AgrAgreement_Fct
     WHERE     (@StartDate BETWEEN BudgetStartDate AND BudgetEndDate OR
               @EndDate BETWEEN BudgetStartDate AND BudgetEndDate )
     GROUP BY AgreementId          
     UNION
     SELECT    AgreementId,
               MAX(BudgetYear) AS MAXYear
     FROM      dbo.AgrAgreement_Fct
     WHERE     BudgetEndDate < @EndDate
     GROUP BY AgreementId 
     UNION
     SELECT    AgreementId,
               MIN(BudgetYear) AS MAXYear
     FROM      dbo.AgrAgreement_Fct
     WHERE     BudgetEndDate < @EndDate
     GROUP BY AgreementId ) VWB
     GROUP BY VWB.AgreementId ) SRCHBGT 
          INNER JOIN dbo.AgrAgreement_Fct VBGT ON
                    SRCHBGT.AgreementId = VBGT.AgreementId AND SRCHBGT.BudgetYear = VBGT.BudgetYear 
          INNER JOIN (SELECT  F.AgreementId,
                              MIN(F.ProjectStartDate) AS ProjectStartDate,
                              MAX(F.ProjectEndDate) AS ProjectEndDate,
                              SUM(F.DirectBudget + F.IndirectBudget) AS AwardedAmount
                    FROM      dbo.AgrAgreement_Fct F
                    GROUP BY  F.AgreementId)  PROJ ON SRCHBGT.AgreementId = PROJ.AgreementId       
     
     RETURN
END
go
IF OBJECT_ID('dbo.fnGetFactTableBudget') IS NOT NULL
    PRINT '<<< CREATED FUNCTION dbo.fnGetFactTableBudget >>>'
ELSE
    PRINT '<<< FAILED CREATING FUNCTION dbo.fnGetFactTableBudget >>>'
go